﻿using BCCommon;
using BCEntity.TMS.MoveHouseConfig.MoveHouseSpecificationConfig;
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BCData.TMS.MoveHouseConfig.MoveHouseSpecificationConfig
{
    public class MoveHouseSpecificationConfigData : IMoveHouseSpecificationConfigData
    {
        private readonly string SQL_QUERY_VIEW = @" SELECT 
                                                  A.MoveHouseSpecificationConfigId,
                                                  A.SpecificationName,
                                                  A.SpecificationPinYin,
                                                  A.MoveHouseConfigId,
                                                  A.MoveHouseConfigName,
                                                  A.ParentMoveHouseConfigId,
                                                  A.ParentMoveHouseConfigName,
                                                  A.Remark,
                                                  A.Enabled,
                                                  A.CreateAdminId,
                                                  A.CreateAdminName,
                                                  A.CreateTime,
                                                  A.UpdateTime,
                                                  A.EditAdminId,
                                                  A.EditAdminName
                                                FROM
                                                  movehousespecificationconfig A ";
        private readonly string SQL_DELETE = @" DELETE FROM movehousespecificationconfig ";
        private readonly IDatabaseContext database;
        public MoveHouseSpecificationConfigData(IDatabaseContext database)
        {
            this.database = database;
        }

        public async Task<bool> IsExist(string specificationName, long moveHouseConfigId, long? moveHouseSpecificationConfigId)
        {
            var sqlQuery = $" SELECT COUNT(1) FROM movehousespecificationconfig WHERE SpecificationName=?SpecificationName AND MoveHouseConfigId=?MoveHouseConfigId ";
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            if (moveHouseSpecificationConfigId.HasValue)
            {
                wheres.Add("MoveHouseSpecificationConfigId != ?MoveHouseSpecificationConfigId");
                parameters.Add("?MoveHouseSpecificationConfigId", moveHouseSpecificationConfigId.Value, DbType.Int64);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $" AND {string.Join(" AND ", wheres)}";
            }
            var sql = $" {sqlQuery}{whereSql} ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<int>(sql, new { specificationName, moveHouseConfigId, moveHouseSpecificationConfigId }) > 0;
        }

        public async Task<MoveHouseSpecificationConfigEntity> InsertAsync(MoveHouseSpecificationConfigEntity entity)
        {
            return await database.Master.InsertAsync(entity, database.Transaction);
        }

        public async Task<MoveHouseSpecificationConfigEntity> UpdateAsync(MoveHouseSpecificationConfigEntity entity)
        {
            return await database.Master.UpdateAsync(entity, database.Transaction);
        }

        public async Task<MoveHouseSpecificationConfigEntity> GetSingle(long moveHouseSpecificationConfigId)
        {
            var sql = typeof(MoveHouseSpecificationConfigEntity).GetSelectSqlByPrimaryKeys();
            return await database.Slave.QueryFirstOrDefaultAsync<MoveHouseSpecificationConfigEntity>(sql, new { moveHouseSpecificationConfigId });
        }

        public async Task<MoveHouseSpecificationConfigViewEntity> GetSingleView(long moveHouseSpecificationConfigId)
        {
            var sql = @" SELECT * FROM (
                            SELECT A.MoveHouseSpecificationConfigId,A.MoveHouseSpecificationConfigOverWriteId,A.SpecificationName,A.SpecificationPinYin, 
                            B.MoveHouseConfigId,B.MoveHouseConfigName,A.AgentRecordId,A.ProvinceCode,A.CityCode,A.CountyCode,A.Remark,A.Enabled,
							A.CreateTime,A.CreateAdminId,A.CreateAdminName,A.UpdateTime,A.EditAdminId,A.EditAdminName,
                            A.ParentMoveHouseConfigId,A.ParentMoveHouseConfigName 
                            FROM MoveHouseSpecificationConfigOverWrite A
                            LEFT JOIN MoveHouseSpecificationConfig B ON A.MoveHouseSpecificationConfigId=B.MoveHouseSpecificationConfigId
							WHERE B.MoveHouseSpecificationConfigId=?MoveHouseSpecificationConfigId
                            UNION ALL 
                            SELECT A.MoveHouseSpecificationConfigId,NULL AS MoveHouseSpecificationConfigOverWriteId,A.SpecificationName,A.SpecificationPinYin,
                                A.MoveHouseConfigId,A.MoveHouseConfigName,NULL AS AgentRecordId,NULL AS ProvinceCode,NULL AS CityCode,NULL AS CountyCode,
                                A.Remark,A.Enabled,A.CreateTime,A.CreateAdminId,A.CreateAdminName,A.UpdateTime,A.EditAdminId,A.EditAdminName 
                                A.ParentMoveHouseConfigId,A.ParentMoveHouseConfigName 
                            FROM MoveHouseSpecificationConfig A
							WHERE A.MoveHouseSpecificationConfigId=?MoveHouseSpecificationConfigId
                            ) T ";
            return await database.Slave.QueryFirstOrDefaultAsync<MoveHouseSpecificationConfigViewEntity>(sql, new { moveHouseSpecificationConfigId });
        }
        public async Task<IEnumerable<MoveHouseSpecificationConfigViewEntity>> GetList(long moveHouseConfigId, long? agentRecordId,
            string provinceCode, string cityCode, string countyCode, bool? isEnable, bool isShowMain)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (isEnable.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", isEnable.Value, DbType.Boolean);
            }
            wheres.Add("A.MoveHouseConfigId = ?MoveHouseConfigId");
            parameters.Add("?MoveHouseConfigId", moveHouseConfigId, DbType.Int64);
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            List<string> wheresTwo = new List<string>();
            if (agentRecordId.HasValue)
            {
                wheresTwo.Add("A.AgentRecordId = ?AgentRecordId");
                parameters.Add("?AgentRecordId", agentRecordId.Value, DbType.Int64);
            }
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheresTwo.Add("A.ProvinceCode = ?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheresTwo.Add("A.CityCode = ?CityCode");
                parameters.Add("?CityCode", cityCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheresTwo.Add("A.CountyCode = ?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
            string whereSqlTwo = string.Empty;
            if (wheresTwo.Count > 0)
            {
                whereSqlTwo = $"AND {string.Join(" AND ", wheresTwo)}";
            }
            string whereSqlThree = string.Empty;
            if (agentRecordId.HasValue && agentRecordId.Value > 0)
            {
                if (!isShowMain)
                {
                    whereSqlThree = " WHERE T.AgentRecordId IS NOT NULL ";
                }
            }
            var sqlQuery = string.Format(@" SELECT * FROM (
                                SELECT A.MoveHouseSpecificationConfigId,A.MoveHouseSpecificationConfigOverWriteId,A.SpecificationName,A.SpecificationPinYin, 
                                B.MoveHouseConfigId,B.MoveHouseConfigName,A.AgentRecordId,A.ProvinceCode,A.CityCode,A.CountyCode,A.Remark,A.Enabled,
								A.CreateTime,A.CreateAdminId,A.CreateAdminName,A.UpdateTime,A.EditAdminId,A.EditAdminName,
                                A.ParentMoveHouseConfigId,A.ParentMoveHouseConfigName 
                                FROM MoveHouseSpecificationConfigOverWrite A
                                LEFT JOIN MoveHouseSpecificationConfig B ON A.MoveHouseSpecificationConfigId=B.MoveHouseSpecificationConfigId
								{0} {1}
                                UNION ALL 
                                SELECT A.MoveHouseSpecificationConfigId,NULL AS MoveHouseSpecificationConfigOverWriteId,A.SpecificationName,A.SpecificationPinYin,
                                    A.MoveHouseConfigId,A.MoveHouseConfigName,NULL AS AgentRecordId,NULL AS ProvinceCode,NULL AS CityCode,NULL AS CountyCode,
                                    A.Remark,A.Enabled,A.CreateTime,A.CreateAdminId,A.CreateAdminName,A.UpdateTime,A.EditAdminId,A.EditAdminName,
                                    A.ParentMoveHouseConfigId,A.ParentMoveHouseConfigName 
                                FROM MoveHouseSpecificationConfig A
							    {0}
                                ) T {2}", whereSql, whereSqlTwo, whereSqlThree);
            string sql = $"{sqlQuery} ORDER BY T.MoveHouseSpecificationConfigId DESC ";
            return await this.database.Slave.QueryAsync<MoveHouseSpecificationConfigViewEntity>(sql, parameters);
        }
        public async Task<(IEnumerable<MoveHouseSpecificationConfigViewEntity>, int)> Query(long moveHouseConfigId, long? agentRecordId,
            string provinceCode, string cityCode, string countyCode, bool? isEnable, bool isShowMain, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            if (isEnable.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", isEnable.Value, DbType.Boolean);
            }
            wheres.Add("A.MoveHouseConfigId = ?MoveHouseConfigId");
            parameters.Add("?MoveHouseConfigId", moveHouseConfigId, DbType.Int64);
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            List<string> wheresTwo = new List<string>();
            if (agentRecordId.HasValue)
            {
                wheresTwo.Add("A.AgentRecordId = ?AgentRecordId");
                parameters.Add("?AgentRecordId", agentRecordId.Value, DbType.Int64);
            }
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheresTwo.Add("A.ProvinceCode = ?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheresTwo.Add("A.CityCode = ?CityCode");
                parameters.Add("?CityCode", cityCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheresTwo.Add("A.CountyCode = ?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
            string whereSqlTwo = string.Empty;
            if (wheresTwo.Count > 0)
            {
                whereSqlTwo = $"AND {string.Join(" AND ", wheresTwo)}";
            }
            string whereSqlThree = string.Empty;
            if (agentRecordId.HasValue && agentRecordId.Value > 0)
            {
                if (!isShowMain)
                {
                    whereSqlThree = " WHERE T.AgentRecordId IS NOT NULL ";
                }
            }
            var sqlQuery = string.Format(@"  SELECT * FROM (
                                SELECT A.MoveHouseSpecificationConfigId,A.MoveHouseSpecificationConfigOverWriteId,A.SpecificationName,A.SpecificationPinYin, 
                                B.MoveHouseConfigId,B.MoveHouseConfigName,A.AgentRecordId,A.ProvinceCode,A.CityCode,A.CountyCode,A.Remark,A.Enabled,
								A.CreateTime,A.CreateAdminId,A.CreateAdminName,A.UpdateTime,A.EditAdminId,A.EditAdminName,
                                    A.ParentMoveHouseConfigId,A.ParentMoveHouseConfigName 
                                FROM MoveHouseSpecificationConfigOverWrite A
                                LEFT JOIN MoveHouseSpecificationConfig B ON A.MoveHouseSpecificationConfigId=B.MoveHouseSpecificationConfigId
								{0} {1}
                                UNION ALL 
                                SELECT A.MoveHouseSpecificationConfigId,NULL AS MoveHouseSpecificationConfigOverWriteId,A.SpecificationName,A.SpecificationPinYin,
                                    A.MoveHouseConfigId,A.MoveHouseConfigName,NULL AS AgentRecordId,NULL AS ProvinceCode,NULL AS CityCode,NULL AS CountyCode,
                                    A.Remark,A.Enabled,A.CreateTime,A.CreateAdminId,A.CreateAdminName,A.UpdateTime,A.EditAdminId,A.EditAdminName,
                                    A.ParentMoveHouseConfigId,A.ParentMoveHouseConfigName 
                                FROM MoveHouseSpecificationConfig A
							    {0}
                                ) T {2}", whereSql, whereSqlTwo, whereSqlThree);
            var sqlCount = string.Format(@" SELECT COUNT(1) FROM (
                                SELECT A.MoveHouseSpecificationConfigId,A.MoveHouseSpecificationConfigOverWriteId,A.SpecificationName,A.SpecificationPinYin, 
                                B.MoveHouseConfigId,B.MoveHouseConfigName,A.AgentRecordId,A.ProvinceCode,A.CityCode,A.CountyCode,A.Remark,A.Enabled,
								A.CreateTime,A.CreateAdminId,A.CreateAdminName,A.UpdateTime,A.EditAdminId,A.EditAdminName,
                                    A.ParentMoveHouseConfigId,A.ParentMoveHouseConfigName 
                                FROM MoveHouseSpecificationConfigOverWrite A
                                LEFT JOIN MoveHouseSpecificationConfig B ON A.MoveHouseSpecificationConfigId=B.MoveHouseSpecificationConfigId
								{0} {1}
                                UNION ALL 
                                SELECT A.MoveHouseSpecificationConfigId,NULL AS MoveHouseSpecificationConfigOverWriteId,A.SpecificationName,A.SpecificationPinYin,
                                    A.MoveHouseConfigId,A.MoveHouseConfigName,NULL AS AgentRecordId,NULL AS ProvinceCode,NULL AS CityCode,NULL AS CountyCode,
                                    A.Remark,A.Enabled,A.CreateTime,A.CreateAdminId,A.CreateAdminName,A.UpdateTime,A.EditAdminId,A.EditAdminName,
                                    A.ParentMoveHouseConfigId,A.ParentMoveHouseConfigName 
                                FROM MoveHouseSpecificationConfig A
							    {0}
                                ) T {2};", whereSql, whereSqlTwo, whereSqlThree);
            string sql = $@"{sqlQuery} ORDER BY T.MoveHouseSpecificationConfigId DESC 
                            LIMIT {(pageIndex - 1) * pageSize}, {pageSize};{sqlCount}";
            return await this.database.Slave.QueryPageableListAsync<MoveHouseSpecificationConfigViewEntity>(sql, parameters);
        }

        public async Task<int> DeleteAsync(long moveHouseSpecificationConfigId)
        {
            var sql = $" {SQL_DELETE} WHERE MoveHouseSpecificationConfigId=?MoveHouseSpecificationConfigId ";
            return await database.Master.ExecuteAsync(sql, new { moveHouseSpecificationConfigId }, database.Transaction);
        }

        public async Task<IEnumerable<MoveHouseSpecificationConfigEntity>> GetList(long moveHouseConfigId, bool? isEnable)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            if (isEnable.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", isEnable.Value, DbType.Boolean);
            }
            wheres.Add("A.MoveHouseConfigId = ?MoveHouseConfigId");
            parameters.Add("?MoveHouseConfigId", moveHouseConfigId, DbType.Int64);
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            var sql = $" {SQL_QUERY_VIEW} {whereSql} ";
            return await database.Slave.QueryAsync<MoveHouseSpecificationConfigEntity>(sql, parameters);
        }

        public async Task<IEnumerable<MoveHouseSpecificationConfigEntity>> GetAllList(long? moveHouseConfigId, bool? isEnable)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (isEnable.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", isEnable.Value, DbType.Boolean);
            }
            if (moveHouseConfigId.HasValue)
            {
                wheres.Add("A.ParentMoveHouseConfigId = ?ParentMoveHouseConfigId");
                parameters.Add("?ParentMoveHouseConfigId", moveHouseConfigId, DbType.Int64);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            var sql = $" {SQL_QUERY_VIEW} {whereSql} ";
            return await database.Slave.QueryAsync<MoveHouseSpecificationConfigEntity>(sql, parameters);
            //    var sqlQuery = string.Format(@" SELECT * FROM (
            //                        SELECT A.MoveHouseSpecificationConfigId,A.MoveHouseSpecificationConfigOverWriteId,A.SpecificationName,A.SpecificationPinYin, 
            //                        B.MoveHouseConfigId,B.MoveHouseConfigName,A.AgentRecordId,A.ProvinceCode,A.CityCode,A.CountyCode,A.Price,A.Remark,A.Enabled,
            //A.CreateTime,A.CreateAdminId,A.CreateAdminName,A.UpdateTime,A.EditAdminId,A.EditAdminName,
            //                            A.ParentMoveHouseConfigId,A.ParentMoveHouseConfigName 
            //                        FROM MoveHouseSpecificationConfigOverWrite A
            //                        LEFT JOIN MoveHouseSpecificationConfig B ON A.MoveHouseSpecificationConfigId=B.MoveHouseSpecificationConfigId
            //{0} {1}
            //                        UNION ALL 
            //                        SELECT A.MoveHouseSpecificationConfigId,NULL AS MoveHouseSpecificationConfigOverWriteId,A.SpecificationName,A.SpecificationPinYin,
            //                            A.MoveHouseConfigId,A.MoveHouseConfigName,NULL AS AgentRecordId,NULL AS ProvinceCode,NULL AS CityCode,NULL AS CountyCode,A.Price,
            //                            A.Remark,A.Enabled,A.CreateTime,A.CreateAdminId,A.CreateAdminName,A.UpdateTime,A.EditAdminId,A.EditAdminName,
            //                            A.ParentMoveHouseConfigId,A.ParentMoveHouseConfigName 
            //                        FROM MoveHouseSpecificationConfig A
            //   {0}
            //                        ) T {2}", whereSql, whereSqlTwo, whereSqlThree);
            //    string sql = $"{sqlQuery} ORDER BY T.MoveHouseSpecificationConfigId DESC ";
            //    return await this.database.Slave.QueryAsync<MoveHouseSpecificationConfigViewEntity>(sql, parameters);
        }


    }
}
